package com.fc.authentication.core.migrate.process;


import com.fc.authentication.core.migrate.dialects.Dialect;
import com.fc.authentication.core.migrate.exception.MigratorException;
import com.fc.authentication.core.migrate.option.None;
import com.fc.authentication.core.migrate.option.Option;
import com.fc.authentication.core.migrate.utils.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

/**
 * a class used to migrate the database.
 * 
 * @author Chris
 */
public class Migrator {
    
    /** 
     * indicates the string that marks the start of a sql comment.
     */
    private static final String SQL_COMMENT = "--";
    
    /**
     * local instance of the logger.
     */
    private static final Logger LOG = LoggerFactory.getLogger(Migrator.class);
    
    /**
     * the name of the subdirectory containing your upgrade scripts
     */
    private static final String UPGRADE_DIRECTORY = "upgrade";
    
    /**
     * the name of the subdirectory containing the clean scripts
     */
    private static final String CLEAN_DIRECTORY = "clean";

    /**
     * RSSSS-423 - Temporary limit to the size of SQLs that can be logged 
     */
    public static final int SQL_LENGTH_LIMIT = (20*1024); 
    
    /**
     * comparator used to sort the files
     */
    private static final WrappedComparator<File, String> SORTER = new WrappedComparator<File, String>( new VersionedStringComparator() ) {
        @Override
        public String unwrap(File object) {
            return object.getName();
        }
    };
    
    
    private Connection _connection;
    private String     _versionTableName;
    private Dialect _dialect;
    private File       _root;
    
    
    /**
     * create's an instance of this migrator using the specified connection and a
     * string containing the sql to create the DB table.
     * 
     * @param conn the connection to execute the migration on
     * @param root the file root
     * @param dialect the dialect to use
     */
    public Migrator(Connection conn, File root, Dialect dialect, String versionTable) {
        _connection       = conn;
        _dialect          = dialect;
        _root             = root;
        _versionTableName = versionTable;
        LOG.debug( "Migrator conn=[{}], root=[{}], table=[{}] ", conn, root, versionTable);
    }
    
    
    /**
     * initializes the table
     * @throws SQLException sqlException
     */
    public void initialize() throws SQLException {
        if (!_dialect.hasVersionTable(_connection, _versionTableName)) {
            _dialect.createVersionTable(_connection, _versionTableName);
        }
    }
    
    
    /**
     * run's the upgrade procedure to update to parameter version
     * @throws SQLException sqlException
     * @throws IOException  ioException
     * @throws MigratorException migratiorException
     */
    public void upgrade(int toVersion) throws SQLException, IOException, MigratorException {
        File upgradeDirectory = new File(_root, UPGRADE_DIRECTORY);
        int fromVersion       = _dialect.getVersion(_connection, _versionTableName);
        
        if (toVersion <= fromVersion) { 
            throw new MigratorException(String.format("Can't upgrade from version [{}] to version [{}]", fromVersion, toVersion));
        }
        
        File[] toRun          = getFiles( upgradeDirectory, toVersion, fromVersion );
        
        LOG.info( "found [{}] scripts to run for upgrade in directory [{}] from version [{}]", toRun.length, upgradeDirectory, fromVersion );
        runUpgradeScripts(toRun);
        
    }

    /**
     * Runs the list of sql upgrade scripts.
     * @param scripts scripts
     * @throws IOException ioException
     * @throws SQLException sqlException
     */
    private void runUpgradeScripts(File[] scripts) throws IOException, SQLException {
        if (scripts.length > 0) {
            for (File script : scripts) {
                int version = VersionedStringComparator.getVersion( script.getName() ).get();
                LOG.info( "executing database upgrade script '[{}]' as version [{}]", script.getName(), version );
                
                for (String sql : loadSql( script )) {
                    if ( sql.length() <= SQL_LENGTH_LIMIT ) {
                        LOG.debug( "executing sql: [{}]", sql );
                    } else {
                        LOG.debug( "[NOTE: SQL Truncated for Logging Purposes Only]\nexecuting sql: [{}]", sql.substring(0, SQL_LENGTH_LIMIT));
                    }
                    _dialect.executeSql(_connection, sql);
                }
                
                LOG.debug( "updating database version to version [{}]", version );
                _dialect.setVersion(_connection, version, _versionTableName);
                if(!_connection.getAutoCommit()) {
                    _connection.commit();
                }
            }
        }
    }
    
    /**
     * run's the upgrade procedure
     * @throws SQLException 
     * @throws IOException 
     */
    public void upgrade() throws SQLException, IOException {
        File upgradeDirectory = new File(_root, UPGRADE_DIRECTORY);
        int fromVersion       = _dialect.getVersion(_connection, _versionTableName);
        File[] toRun          = getFiles( upgradeDirectory, fromVersion );
        
        LOG.info( "found [{}] scripts to run for upgrade in directory [{}] from version [{}]", toRun.length, upgradeDirectory, fromVersion );
        runUpgradeScripts(toRun);
    }
    
    
    /** 
     * run's all the scripts in the clean directory
     * @throws SQLException 
     * @throws IOException 
     */
    public void clean() throws SQLException, IOException {
        
        File cleanDirectory = new File(_root, CLEAN_DIRECTORY);
        int currentVersion  = _dialect.getVersion(_connection, _versionTableName);
        File[] toRun        = reverse(getFiles( cleanDirectory, currentVersion, 0 ));
        
        LOG.info( "found [{}] scripts to run to execute a clean directive in directory [{}]", toRun.length, cleanDirectory );
        runCleanScripts(toRun);
    }
   
    
    /**
     * loads all the sql scripts in the specified file
     * 
     * @param script
     * @return List<String> sqlList
     * @throws IOException 
     */
    private List<String> loadSql( File script ) throws IOException {
    	
    	FileInputStream fis  = null;
    	List<String> out      = new ArrayList<String>();
    	
    	try {
    		fis   = new FileInputStream(script);
    		BufferedReader br     = new BufferedReader(new InputStreamReader(fis, "UTF-8"));
    		String line           = null;
    		StringBuilder builder = new StringBuilder();
    		boolean inBlock       = false;
    		boolean blockEnd      = false;
    		int blockIndent       = 0;

    		while (null != (line = br.readLine())) {
    			line = line.trim();
    			if (line.length() > 0) {
    				if (!line.startsWith( SQL_COMMENT )) {
    					builder.append( line );

    					// RSSSS-356
    					if ( line.toUpperCase().contains("END;") ) {
    						if  ( line.trim().length() == "END;".length() ) {
    							blockEnd = true;
    						} else {
    							blockEnd = line.toUpperCase().contains(" END;");
    						}
    					}

    					if (line.toUpperCase().contains("CREATE OR REPLACE")) inBlock = true;
    					if (line.toUpperCase().contains("CREATE PROCEDURE")) inBlock = true;
    					if (line.toUpperCase().contains("BEGIN") || line.toUpperCase().contains("CREATE OR REPLACE PACKAGE") || 
    							line.toUpperCase().contains("CREATE PACKAGE")) { // RSSSS-356
    						inBlock = true;
    						blockIndent++;
    					}

    					if (blockEnd && inBlock) {
    						blockIndent--;
    					}

    					// if we're not in a block and it ends with ';', then add the line - the other case
    					// is if we're in a block, the line is ending, and we're and 0 indentation (for nested BEGIN
    					// end statements)
    					if ((line.endsWith(";") && !inBlock) || (inBlock && blockEnd && 0 == blockIndent)) {

    						// if we need to close it out with a semi
    						if (inBlock && !line.endsWith(";")) builder.append(";");

    						// code blocks (stored procedures, etc) need to end with 'END;', regular statements
    						// do not. not sure why.
    						if (!inBlock && line.endsWith(";")) {
    							out.add(StringUtils.notEndingWith(builder.toString(), ";"));
    						} else {
    							out.add(builder.toString());
    						}


    						builder     = new StringBuilder();
    						inBlock     = false;
    						blockEnd    = false;
    						blockIndent = 0;
    					} else {
    						builder.append("\n");
    						if ( blockIndent > 0 ) blockEnd = false;
    					}
    				}
    			}
    		}
    		// RSSSS-423
    		if ( builder.length() > 0 ) { 
    			if ( builder.length() <= SQL_LENGTH_LIMIT ) {
    				LOG.warn("The SQL does not seem to have a proper 'end'. Executing it against database nonetheless. SQL: [{}]", builder.toString());
    			} else {
    				LOG.warn("The SQL does not seem to have a proper 'end'. Executing it against database nonetheless.");
    			}
    			out.add(builder.toString());
    		}
    	}
    	finally {
    		fis.close();
    	}
    	return out;
    }

    
    /**
     * returns all the versioned file in the specified directory
     * @param directory
     * @return
     */
    private File[] getFiles( File directory, int version ) {
        File[] files = directory.listFiles( new VersionedFileFilter(version) );
        if (files == null)  return new File[0];
        Arrays.sort( files, SORTER );
        validateFiles( files );
        return files;
    }


    /**
     * Downgrades to parameter version.
     * @param toVersion
     * @throws SQLException
     * @throws IOException
     * @throws MigratorException 
     */
    public void downgrade(int toVersion) throws SQLException, IOException, MigratorException {
        
        File downgradeDirectory = new File(_root, CLEAN_DIRECTORY);
        int fromVersion       = _dialect.getVersion(_connection, _versionTableName);
        
        if (toVersion >= fromVersion) { 
            throw new MigratorException(String.format("Can't downgrade from version [%s]to version [%s]", fromVersion, toVersion));
        }
        
        File[] toRun          = reverse(getFiles( downgradeDirectory, fromVersion, toVersion ));
        
        LOG.info( "found %s scripts to run for downgrade in directory [{}] from version [{}] to version [{}]", toRun.length, downgradeDirectory, fromVersion, toVersion );
        runCleanScripts(toRun);
        
    }
    
    /**
     * Runs the list of clean scripts.
     * @param scripts
     * @throws IOException
     * @throws SQLException
     */
    private void runCleanScripts(File [] scripts) throws IOException, SQLException {
        if (scripts.length > 0) {
            for (File script : scripts) {
                int version = VersionedStringComparator.getVersion( script.getName() ).get();
                LOG.info( "executing database clean script: [{}] as version [{}]", script.getName(), version );
                
                for (String sql : loadSql( script )) {
                    LOG.debug( "executing sql: [{}]", sql );
                    _dialect.executeSql(_connection, sql);
                }
                
                LOG.debug( "downgrading database version to version [{}]", --version );
                _dialect.setVersion(_connection,  version, _versionTableName);
                if(!_connection.getAutoCommit()) {
                    _connection.commit();
                }
            }
        }
    }

    private File [] reverse(File [] files) {
        List<File> list = Arrays.asList(files);
        Collections.reverse(list);
        return list.toArray(new File[0]);
    }

    /**
     * Returns script files from fromVersion to toVersion in version ascendant order.
     * @param directory
     * @param fromVersion the highest version
     * @param toVersion the lowest version
     * @return
     */
    private File[] getFiles(File directory, int fromVersion, int toVersion) {
        File[] files = directory.listFiles( new VersionBetweenFileFilter(fromVersion, toVersion) );
        if (files == null)  return new File[0];
        Arrays.sort( files, SORTER );
        validateFiles( files );
        return files;
    }
    
    /**
     * Validate files list for common mistakes.
     * @param files
     */
    private void validateFiles(File[] files) {
        Option<Integer> previos = new None<Integer>();
        for(File f : files) {
            // Check the file appears to be a sql script.
            if(!f.getName().toLowerCase().endsWith(".sql")) {
                LOG.warn(String.format("Potential error: not a sql file [%s] in migration scripts.", f.getName()));
            }
            // Check the file has version in the name
            Option<Integer> version = VersionedStringComparator.getVersion(f.getName());
            if(!version.hasValue()) {
                LOG.warn(String.format("Potential error: non-versioned file [%s] in migration scripts.", f.getName()));
            } else {
                // Check version number for sanity
                if(version.get() <= 0) {
                    LOG.warn(String.format("Potential error: insane version number [%d] in migration script [%s].", version.get(), f.getName()));
                }
                // Check there is only one script per each version.
                if(previos.hasValue() && previos.get().equals(version.get())) {
                    LOG.warn(String.format("Potential error: several scripts for the same version [%d] in migration scripts.", version.get()));
                }
                previos = version;
            }
        }
        
    }


    /**
     * Returns current version.
     * @return int
     * @throws SQLException
     */
    public int getCurrentVersion() throws SQLException {
        return _dialect.getVersion(_connection, _versionTableName);
    }
    
    /**
     * Closes the db connection, if it exists.
     * @return  nothing
     * @throws SQLException sqlException
     */
    public void closeConnection() throws SQLException    {
    	if (_connection != null ) {
    		_connection.close();
    	}
    }
}
